package com.lw.lost.daos

import android.content.ContentValues
import android.database.Cursor
import com.lw.lost.db.DBlost

class UserDao(private val db: DBlost) {
    
    fun insert(username: String, password: String, name: String, phone: String, status: Int, gender: Int, age: Int, avatar: String? = null): Long {
        val values = ContentValues().apply {
            put("username", username)
            put("password", password)
            put("name", name)
            put("phone", phone)
            put("status", status)
            put("gender", gender)
            put("age", age)
            put("avatar", avatar)
        }
        return db.writableDatabase.insert("users", null, values)
    }

    fun login(username: String, password: String): Cursor {
        return db.readableDatabase.query(
            "users",
            null,
            "username = ? AND password = ?",
            arrayOf(username, password),
            null,
            null,
            null
        )
    }

    fun getUserById(userId: Long): Cursor {
        return db.readableDatabase.query(
            "users",
            null,
            "userId = ?",
            arrayOf(userId.toString()),
            null,
            null,
            null
        )
    }

    fun getAllUsers(): Cursor {
        return db.readableDatabase.rawQuery(
            """
            SELECT * FROM users 
            WHERE status != 2  -- 直接在SQL中过滤掉管理员
            ORDER BY userId DESC
            """,
            null
        )
    }

    fun searchUsers(keyword: String): Cursor {
        val searchPattern = "%$keyword%"
        return db.readableDatabase.rawQuery("""
            SELECT 
                u.userId,
                u.name,
                u.age,
                u.gender,
                u.avatar,
                i.content as interests,
                i.education
            FROM users u
            LEFT JOIN interests i ON u.userId = i.userId
            WHERE u.status = 1
            AND (
                u.name LIKE ? 
                OR i.education LIKE ?
                OR i.content LIKE ?
            )
            ORDER BY u.userId DESC
        """, arrayOf(searchPattern, searchPattern, searchPattern))
    }

    fun updateUserStatus(userId: Long, status: Int): Int {
        val values = ContentValues().apply {
            put("status", status)
        }
        return db.writableDatabase.update(
            "users",
            values,
            "userId = ?",
            arrayOf(userId.toString())
        )
    }

    fun updateUser(userId: Long, values: ContentValues): Int {
        return db.writableDatabase.update(
            "users",
            values,
            "userId = ?",
            arrayOf(userId.toString())
        )
    }

    fun deleteUser(userId: Long): Int {
        return db.writableDatabase.delete(
            "users",
            "userId = ?",
            arrayOf(userId.toString())
        )
    }

    fun updateAvatar(userId: Long, avatarPath: String): Int {
        val values = ContentValues().apply {
            put("avatar", avatarPath)
        }
        return db.writableDatabase.update(
            "users",
            values,
            "userId = ?",
            arrayOf(userId.toString())
        )
    }

    fun getRecommendUsers(currentUserId: Long): Cursor {
        val db = db.readableDatabase
        
        // 获取当前用户信息
        val currentUser = db.query(
            "users",
            arrayOf("age", "gender"),
            "userId = ?",
            arrayOf(currentUserId.toString()),
            null,
            null,
            null
        ).use { cursor ->
            if (cursor.moveToFirst()) {
                Pair(
                    cursor.getInt(cursor.getColumnIndexOrThrow("age")),
                    cursor.getInt(cursor.getColumnIndexOrThrow("gender"))
                )
            } else Pair(0, 0)
        }

        val ageRange = 5 // 年龄范围±5岁
        val minAge = currentUser.first - ageRange
        val maxAge = currentUser.first + ageRange

        // 使用 SQL 查询匹配合适年龄和性别的用户，确保选择所有需要的列
        return db.rawQuery("""
            SELECT 
                u.userId,
                u.name,
                u.age,
                u.gender,
                u.avatar,
                i.content as interests,
                i.education
            FROM users u
            LEFT JOIN interests i ON u.userId = i.userId
            WHERE u.userId != ? 
            AND u.status = 1
            AND u.age BETWEEN ? AND ?
            ORDER BY 
                CASE 
                    WHEN u.gender != ? THEN 2
                    ELSE 1
                END DESC,
                ABS(u.age - ?) ASC
            LIMIT 10
        """, arrayOf(
            currentUserId.toString(),
            minAge.toString(),
            maxAge.toString(),
            currentUser.second.toString(),
            currentUser.first.toString()
        ))
    }

    fun getUserReports(userId: Long): Cursor {
        return db.readableDatabase.rawQuery(
            """
            SELECT content, reportTime 
            FROM reports 
            WHERE userId = ? 
            ORDER BY reportTime DESC
            """,
            arrayOf(userId.toString())
        )
    }

    fun searchPreciseMatch(
        gender: Int,
        minAge: Int,
        maxAge: Int,
        education: String,
        interests: String
    ): Cursor {
        // 将兴趣内容按空格分割成多个关键词
        val interestKeywords = interests.split(" ")
            .filter { it.isNotEmpty() }
            .map { "%$it%" }

        // 构建兴趣匹配条件
        val interestConditions = interestKeywords.joinToString(" OR ") { 
            "i.content LIKE ?" 
        }

        // 构建查询参数
        val queryParams = mutableListOf(
            gender.toString(),
            minAge.toString(),
            maxAge.toString(),
            education
        )
        // 添加兴趣关键词参数
        queryParams.addAll(interestKeywords)

        return db.readableDatabase.rawQuery("""
            SELECT 
                u.userId,
                u.name,
                u.age,
                u.gender,
                u.avatar,
                i.content as interests,
                i.education
            FROM users u
            LEFT JOIN interests i ON u.userId = i.userId
            WHERE u.status = 1
            AND u.gender = ?
            AND u.age BETWEEN ? AND ?
            AND i.education = ?
            AND ($interestConditions)
            ORDER BY u.userId DESC
        """, queryParams.toTypedArray())
    }

    fun getFriends(userId: Long): Cursor {
        return db.writableDatabase.rawQuery("""
            SELECT DISTINCT u.*
            FROM users u
            INNER JOIN match_records m ON 
                (m.userId = ? AND m.matchedUserId = u.userId) OR 
                (m.userId = u.userId AND m.matchedUserId = ?)
            WHERE u.status = 1
            ORDER BY u.name
        """, arrayOf(userId.toString(), userId.toString()))
    }

    fun getUnreadCount(senderId: Long): Int {
        return db.writableDatabase.rawQuery("""
            SELECT COUNT(*) as count
            FROM messages
            WHERE senderId = ? AND isRead = 0
        """, arrayOf(senderId.toString())).use { cursor ->
            if (cursor.moveToFirst()) {
                cursor.getInt(cursor.getColumnIndexOrThrow("count"))
            } else {
                0
            }
        }
    }
} 